package com.apes.fn.scm.merchant.settlement.repository;

import com.apes.fn.scm.merchant.account.model.MerchantAccountSettlement;
import com.apes.fn.scm.merchant.settlement.model.MerchantSettlement;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @BelongsProject: apes-server
 * @BelongsPackage: com.apes.fn.scm.merchant.settlement.repository
 * @ClassName: MerchantSettlementRepository
 * @Author: pyb
 * @Description: 赋能商户结算单数据层
 * @CreateTime: 2020-10-29 15:04
 * @Version: 1.0
 */
public interface MerchantSettlementRepository extends CustomRepository<MerchantSettlement, String> {

    /**
     * fetch data by max end date
     *
     * @param merchantId merchant id
     * @return MerchantSettlement
     */
    @Query("select T from MerchantSettlement  T where T.reconcileEndDate=(select max(reconcileEndDate)from MerchantSettlement where merchant.id=?1) and T.merchant.id=?1")
    List<MerchantSettlement> findByMaxReconcileEndDate(String merchantId);

    /**
     * fetch data by merchant
     *
     * @param merchantId merchant id
     * @param startDate  date
     * @param endDate    date
     * @return MerchantSettlement
     */
    @Query("select T from MerchantSettlement  T where T.merchant.id=?1 and T.state='squareAccounts' and" +
            "((T.reconcileEndDate>?2 and T.reconcileEndDate<=?3) or(T.reconcileBeginDate<?2 and T.reconcileEndDate>=?3)or(T.reconcileBeginDate>?2 and T.reconcileBeginDate<=?3))")
    List<MerchantSettlement> findByMerchantAndDate(String merchantId, Date startDate, Date endDate);

    /**
     * fetch data by merchant
     *
     * @param merchantId merchant id
     * @return MerchantSettlement
     */
    @Query(value = "SELECT T.ID id,T.MERCHANT_ID merchant,TO_CHAR(T.RECONCILE_BEGIN_DATE,'yyyy-MM-dd') reconcileBeginDate,TO_CHAR(T.RECONCILE_END_DATE,'yyyy-MM-dd') reconcileEndDate,T.STATE state " +
            "from fn_merchant_settlement T " +
            "where T.merchant_id=?1 and T.state='squareAccounts' and ((T.reconcile_end_date>?2 and T.reconcile_end_date<=?3) or(T.reconcile_begin_date<?2 and T.reconcile_end_date>=?3)or(T.reconcile_begin_date>?2 and T.reconcile_begin_date<=?3))" +
            "UNION ALL SELECT T.ID id,T.MERCHANT_ID merchant,TO_CHAR(T.RECONCILE_BEGIN_DATE,'yyyy-MM-dd') reconcileBeginDate,TO_CHAR(T.RECONCILE_END_DATE,'yyyy-MM-dd') reconcileEndDate,T.STATE state " +
            "from merchant_account_settlement T " +
            "where T.merchant_id=?1 and T.state_accounts='squareAccounts' and ((T.reconcile_end_date>?2 and T.reconcile_end_date<=?3) or(T.reconcile_begin_date<?2 and T.reconcile_end_date>=?3)or(T.reconcile_begin_date>?2 and T.reconcile_begin_date<=?3))",nativeQuery = true
            ,countQuery = "SELECT COUNT(1) FROM merchant_account_settlement T where T.merchant_id=?1 and (T.state_accounts='squareAccounts' OR T.state_accounts='squareAccounts') and ((T.reconcile_end_date>?2 and T.reconcile_end_date<=?3) or(T.reconcile_begin_date<?2 and T.reconcile_end_date>=?3)or(T.reconcile_begin_date>?2 and T.reconcile_begin_date<=?3))")
    Page<Map> findByMerchantAndState(String merchantId, Date startDate, Date endDate,Pageable pageable);


    /**
     * fetch data by merchant
     *
     * @param merchantId merchant id
     * @param reconcileDate    date
     * @return MerchantSettlement
     */
    @Query("select T from MerchantSettlement  T where T.merchant.id=?1 and T.state<>'squareAccounts'and T.state<>'confirmation'  and T.reconcileBeginDate>?2")
    List<MerchantSettlement> findByMerchantAndReconcileDate(String merchantId, Date reconcileDate);



}
